from fastapi import APIRouter, Depends, HTTPException
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import func, select, distinct
from datetime import datetime, timedelta
from typing import List, Dict
from collections import defaultdict

from app.db.session import get_db
from app.db.models import Click, URL
from app.routes.auth import oauth2_scheme

router = APIRouter()

@router.get("/stats/{short_code}")
async def get_url_stats(
    short_code: str,
    db: AsyncSession = Depends(get_db),
    token: str = Depends(oauth2_scheme)
):
    # 获取URL信息
    url = await db.execute(
        select(URL).where(URL.short_code == short_code)
    )
    url = url.scalar_one_or_none()
    if not url:
        raise HTTPException(status_code=404, detail="URL not found")

    # 获取点击统计
    now = datetime.utcnow()
    last_24h = now - timedelta(days=1)
    last_7d = now - timedelta(days=7)
    last_30d = now - timedelta(days=30)

    # 总点击量
    total_clicks = await db.execute(
        select(func.count()).where(Click.url_id == url.id)
    )
    total_clicks = total_clicks.scalar()

    # 独立IP数
    unique_ips = await db.execute(
        select(func.count(distinct(Click.ip_address))).where(Click.url_id == url.id)
    )
    unique_ips = unique_ips.scalar()

    # 按时间段统计
    hourly_stats = await db.execute(
        select(
            func.date_format(Click.clicked_at, '%Y-%m-%d %H:00:00').label('hour'),
            func.count().label('count')
        ).where(
            Click.url_id == url.id,
            Click.clicked_at >= last_24h
        ).group_by('hour').order_by('hour')
    )
    hourly_stats = dict(hourly_stats.all())

    # 浏览器统计
    browser_stats = await db.execute(
        select(
            func.regexp_replace(Click.user_agent, '^.*?([A-Za-z]+)/.*$', '\\1').label('browser'),
            func.count().label('count')
        ).where(Click.url_id == url.id).group_by('browser')
    )
    browser_stats = dict(browser_stats.all())

    # 地理位置统计（简单按IP前缀分类）
    location_stats = await db.execute(
        select(
            func.substring_index(Click.ip_address, '.', 2).label('location'),
            func.count().label('count')
        ).where(Click.url_id == url.id).group_by('location')
    )
    location_stats = dict(location_stats.all())

    return {
        "url_info": {
            "original_url": url.original_url,
            "short_code": url.short_code,
            "created_at": url.created_at,
            "expires_at": url.expires_at
        },
        "total_clicks": total_clicks,
        "unique_visitors": unique_ips,
        "hourly_stats": hourly_stats,
        "browser_stats": browser_stats,
        "location_stats": location_stats
    } 